"""
Case Type   : Ubtree
Case Name   : 建表插入数据，创建ubtree索引指定pcr_copy from、copy to_分区表
Create At   : 2025-10-11
Owner       : sungang14
Description :
    1:建表;
    2:建索引时指定PCR
    3:插入数据
    4:执行COPY TO命令，导出表数据，构造数据文件
    5:执行COPY FROM命令，向表导入数据
    6:清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
History     :
"""
import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.node_pri = Node(node='PrimaryDbUser')
        status = self.comsh.get_db_cluster_status('status')
        self.assertTrue(status)
        self.table_name = 't_ubtree_case0109'
        self.prepare_data_00 = f"drop table if exists {self.table_name} cascade;"
        self.prepare_data_01 = f"""create table {self.table_name} (id int, name varchar(20)) with (storage_type=ustore)
                                    partition by hash (id) (
                                        partition p0,
                                        partition p1
                                    );"""
        self.prepare_data_02 = f"create index i_ubtree_0109 on {self.table_name} using ubtree(id) with (index_type=pcr);" \
                               f"select relkind, reloptions from pg_class where relname like 'i_ubtree_0109%';"
        self.prepare_data_03 = f"insert into {self.table_name} select g, 'name_'||g from generate_series(1,1000) as g;"
        self.file_abspath = os.path.join(
            macro.DB_BACKUP_PATH, f"{self.table_name}.txt")

    def test_slowsql(self):
        text = '--step1:建表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = "--step2:建索引时指定PCR;expect:成功--"
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step3:插入数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03)
        logger.info(data_cmd)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '-----step4:执行copy to操作;expect:成功-----'
        logger.info(text)
        copy_to_res = self.comsh.execut_db_sql(
            f"copy {self.table_name} to '{self.file_abspath}';")
        logger.info(copy_to_res)
        self.assertIn('COPY 1000', copy_to_res, f'执行失败:{text}')

        text = '-----step5:执行copy from操作;expect:成功-----'
        logger.info(text)
        copy_from_res = self.comsh.execut_db_sql(
            f"delete from {self.table_name};copy {self.table_name} from '{self.file_abspath}';")
        logger.info(copy_from_res)
        self.assertIn('COPY 1000', copy_from_res, f'执行失败:{text}')

    def tearDown(self):
        text = '--step6:清理环境;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        cmd = f'rm -f {self.file_abspath};echo $?;'
        logger.info(cmd)
        cmd_res = self.node_pri.sh(cmd).result()
        logger.info(cmd_res)
        self.assertEqual('0', cmd_res, f'执行失败:{text}')
        logger.info(f'-----{os.path.basename(__file__)} end-----')
